Tanel Poder’s blog: Core IT for geeks and pros

I have released the version 1.06 of Oracle Session Snapper.

If you don’t know what Snapper is, check out those blog entries first:

• http://blog.tanelpoder.com/2007/08/20/oracle-session-snapper-real-time-session-level-performance-stats-for-dbas/
• http://blog.tanelpoder.com/2007/08/26/oracle-session-snapper-part-2-getting-most-out-of-snapper/

Improvements:

• Ability to report instancewide Latch and Enqueue get deltas (useful for testing code efficiency in dev environments where there’s no other activity going on)
• Header format and header options have changed slightly - for making it simpler to quickly paste & graph the data in Excel
• Some bugs have been fixed (and probably new ones introduced)

How to read Snapper output?

Here are the column definitions (sorry for crappy formatting, I need to do some CSS tweaking once I get a chance):

Examples:

(more…)

Couple of years ago an interesting fact floated up in Oracle-L - a regular user with only SELECT privilege on a table can successfully execute a SELECT FOR UPDATE against it, locking all rows and even lock the whole table using LOCK TABLE command. Locking a table in exclusive mode would stall all changes and selects against that table - effectively hanging all applications using that table. Pete Finnigan wrote a review of the issue in this blog entry.

This means that many of the “read only” accounts used by support or reporting users aren’t really that read only - these accounts could be used for a (hopefully) short denial of service attack and leaves another opportunity for human error to cause trouble in production environments. This issue applies both for direct user sessions and dblinks.

This behaviour came as news to me back then and no real solution for this issue has been proposed so far. So, while I do not provide any new information regarding the problem itself, I do propose a solution for it.

First lets start from reproducing the problem case. Note that I use two users, SYSTEM as the table owner and TEST as the “read only” user.

  (more…)

I was doing some low-level security research on Oracle 11g and realized that combining couple little known Oracle’s features can allow anyone with DBA or IMP_FULL_DATABASE rights run any OS command under the same privileges the Oracle processes are running. This allows an attacker to erase files from audit_file_dest or patch the Oracle binary (after setting _disable_image_check to true) or make a dedicated server process a SYSDBA one using a debugger.

I don’t rank this security issue a too critical one as exploiting it requires the attacker to already have high privileges - the BECOME USER privilege in addition to execute rights on KUPP$PROC package used by DataPump. These privileges are included in DBA and IMP_FULL_DATABASE roles by default. So in order to exploit the security flaw you would already have pretty destructive rights ( IMP_FULL_DATABASE has DROP ANY TABLE and such privs in it already ).

However there are few assumptions on what a regular, non-SYSDBA DBA can’t do on modern Oracle versions ( where o7_dictionary_accessibility = false ).

Few of those are:

1. can not alter SYS objects
2. can not shut down & restart database ( for changing audit_trail or remote_login_passwordfile settings )
3. can not grant SYSDBA & SYSOPER privileges
4. can not grant rights on some SYS objects ( which haven’t been granted to DBA role with grant option )
5. can not use oradebug
6. can not run OS commands using Oracle executable rights

Those assumptions do not hold true for now for users with DBA or IMP_FULL_DATABASE role, until the issue has been fixed - or the BECOME USER privilege is revoked from the roles ( which may make full imports fail ).

Here is the test case for Solaris ( but this can be made to work on other Unixes and Windows as well ):

(more…)

I’m having some busy times, so can’t blog anything more serious than another sqlplus trick (which likely has value only to some hardcore sqlplus geeks though).

Ever wanted to load a sqlplus parameter (like linesize, pagesize or arraysize) into a sqlplus define variable?

This can sometimes be helpful for customizing your everyday DBA scripts to output (or not output) some columns based on linesize. Or you may want to use the SQL error code somewhere in your script.

We’ll it’s doable with the getplusparm.sql script:

SQL> def myvar
SP2-0135: symbol myvar is UNDEFINED
SQL>
SQL> @getplusparm linesize myvar
SQL>
SQL> def myvar
DEFINE MYVAR           = “80″ (CHAR)
SQL>  

The above example reads sqlplus parameter “linesize” to a define “myvar”.

The example below uses the sqlcode sqlplus parameter to capture the error code of last command sent to database ( 0 if success ):

SQL> drop table xyz;
drop table xyz
           *
ERROR at line 1:
ORA-00942: table or view does not exist  

SQL>
SQL> @getplusparm sqlcode err
SQL>
SQL> prompt Error code &err
Error code 942
SQL>  

Note that the script creates few temporary files into local directory, you may want to modify the file locations from default for your use (and include an instance name or some variable to avoid race conditions when running multiple sqlplus’es concurrently).

And this is the script text… if you find a way for doing it simpler in sqlplus, let me know :)

set termout off  

def _tmpfile=getplusparm  

spool &_tmpfile..tmp
show &1
spool off  

spool &_tmpfile..chg
prompt c/&1/def &2/
prompt c/&2 /&2=/
spool off  

get &_tmpfile..tmp nolist
@&_tmpfile..chg
save file &_tmpfile..set replace  

@&_tmpfile..set  

set termout on  

While this is probably my most useless post written during my short blogging career, I was satisfied just to find out that such thing is doable. So, all sqlplus geeks out there, enjoy! ;)

Question: How to check instance parameter values in Oracle?

Answer: show parameter xyz

WRONG!

Answer: select value from v$parameter where name = ‘xyz’

WRONG!

These commands show the session level parameter values, which are separate from instance level parameters:

SQL> show parameter session_cached_cursors 

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------------
session_cached_cursors               integer     20 

SQL> select value from v$parameter where name = 'session_cached_cursors'; 

VALUE
---------------------------------------------------------------------------------
20 

V$SYSTEM_PARAMETER is the view which shows instance level parameters (and these are what all new sessions inherit)

 

SQL> select value from v$system_parameter where name = 'session_cached_cursors'; 

VALUE
--------------------------------------------------------------------------------
0 

In this case the difference was due a logon trigger whcih did set session_cached_cursors based on v$session program name.

Note that there is another not widely known gotcha with parameters - multivalue parameters:

There are few parameters which can accept and hold multiple values at a time. For example, utl_file_dir and service_names.

From the following it seems that in my instance there’s a single parameter with multiple comma separated values:

SQL> select value from v$system_parameter where name = 'service_names'; 

VALUE
------------------------------------------------------------------------------------
WEB, HR, OE, MFG, ADMIN 

Actually these values are treated as separate parameter values (of the same parameter name) in V$PARAMETER2 and V$SYSTEM_PARAMETER2:

SQL> select value from v$system_parameter2 where name = 'service_names'; 

VALUE
------------------------------------------------------------------------------------
WEB
HR
OE
MFG
ADMIN 

So, in addition to well known V$PARAMETER, there’s V$PARAMETER2, V$SYSTEM_PARAMETER and V$SYSTEM_PARAMETER2 - and only V$SYSTEM_PARAMETER(2) show you the instance parameters.

And there’s more - V$SPPARAMETER, V$OBSOLETE_PARAMETER and V$PARAMETER_VALID_VALUES (introduced in 10.2) which all can be handy for diagnosing problems and for grilling people at interviews (*evil grin*).

Unlike all my other marvellous posts, this one doesn’t actually have a point - but hey it’s Friday evening in Singapore (or Saturday in Australia) already, so I wish you a happy, parameter-free weekend ;-)

If you have been involved in tuning SQL code which you have never seen before, you are probably familiar with the challenges of understanding what the code is trying to do. This can be especially time consuming when the SQL references lots of views, which reference views, which reference more views etc. So there may be a large information gap between the SQL statement (like select * from some_crazy_10_level_view) and the actual execution plan (referencing 10s of tables, with evidence of query transformations).

So unless you see something really obvious from the execution plan, you need to start mapping the SQL query and view texts back to the physical base tables which Oracle eventually has to access. This can be a tedious and boring (!) process.

The good news is that in Oracle 10.2+ there’s a hidden parameter that can do this mapping task for us. Let’s see an example:

I create a view on a view to illustrate the point:

SQL> create view myview as select * from all_users;

View created.

Now let’s set that parameter _dump_qbc_tree to 1 and run a query against the view:

SQL> alter session set "_dump_qbc_tree"=1;

Session altered.

SQL> select count(*) from myview;

  COUNT(*)
----------
        31

Now let’s look into the server process tracefile:

*** ACTION NAME:() 2007-09-16 12:19:57.500
*** MODULE NAME:(SQL*Plus) 2007-09-16 12:19:57.500
*** SERVICE NAME:(SYS$USERS) 2007-09-16 12:19:57.500
*** SESSION ID:(146.1984) 2007-09-16 12:19:57.500
QCSDMP: -------------------------------------------------------
QCSDMP:  SELECT: (qbc=2B8D1C28)
QCSDMP:    . (COUNT(*)) (opntyp=2 opndty=0)
QCSDMP:  FROM:
QCSDMP:    .MYVIEW
QCSDMP:      VQB:
QCSDMP:        SELECT: (qbc=2B8D163C)
QCSDMP:          .USERNAME
QCSDMP:        FROM:
QCSDMP:          .ALL_USERS
QCSDMP:            VQB:
QCSDMP:              SELECT: (qbc=2B8CAF78)
QCSDMP:                U.NAME (USERNAME)
QCSDMP:              FROM:
QCSDMP:                SYS.TS$ (TTS)
QCSDMP:                SYS.TS$ (DTS)
QCSDMP:                SYS.USER$ (U)

Here it is, the query text generated directly from parse tree, showing the base tables regardless that they had been hidden behind multiple views.

Also there’s few interesting things to note:

(more…)

…or rather thread stack as nowadays decent operating systems execute threads (or tasks as they’re called in Linux kernel).

Anyway, stack trace gives you the ultimate truth on what your program is doing, exactly right now. There are couple of but’s like stack corruptions and missing symbol information which may make the traces less useful for us, but for detailed hang & performance troubleshooting the stack traces are a goldmine.

So, I present another case study - how to diagnose a complete database hang when you can’t even log on to the database.

(more…)

Whenever delivering some Oracle training or running a demo at a conference, I’ve always liked to use the Windows command prompt version of sqlplus.

One reason of course is its easy command line history navigation capability ( press F7 in cmd.exe after entering few commands to see why ).

Another reason is that whenever I want to highlight some part of sqlplus output, I can just drag a selection rectangle around that text. In other words I can “mark” the text - drawing the attention there. Of course as the selection rectangle is really meant for copy & paste operations only, it has several limitations. It’s not persistent, whenever I continue typing, the text “marking” will disappear.

Windows XP has introduced a really cool feature to cmd.exe, which anyone doing presentations involving some command line tool output will appreciate!

Basically XP allows you to persistently select and color command prompt output!

An example of what I’m talking about is here:

(more…)

As you probably already know, the Session Snapper has been designed to be a very easy-to-use performance tool. It is especially useful in database environments where there are no decent performance tools pre-installed and available.

Snapper doesn’t require any setup, all you need is to log on to the database using sqlplus and download snapper.sql script to your computer.

Well, actually the second part is not required, as Oracle sqlplus allows you to run scripts from http and ftp locations!

C:>sqlplus "sys/oracle@ora92 as sysdba" 

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Aug 30 23:00:10 2007 

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved. 

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production 

SQL> @http://www.tanelpoder.com/files/scripts/whoami.sql 

Tanel’s sqlplus http test…
http://www.tanelpoder.com/files/scripts/whoami.sql 

USER                           SYSDATE
—————————— ———
SYS                            30-AUG-07 

NAME
———
ORA92 

INSTANCE_NAME    HOST_NAME
—————- —————————————————————-
ora92            WINDOWS01 

You should be *very* sure that noone can change the scripts on the server without your knowing!!! 

SQL> 

Cool stuff or what? :)

Let’s see how this relates to everyday DBA life…

(more…)

There was a discussion about whether Oracle really allocates all memory for SGA immediately on instance startup or not. And further, whether Oracle allocates memory beyond the SGA_TARET if SGA_MAX_SIZE is larger than it.
It’s worth reading this thread first: http://forums.oracle.com/forums/thread.jspa?threadID=535400&tstart=0

I will paste an edited version of my reply to here as well:

(more…)